﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetRightInPositionToHandleClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetRightInPositionToHandleClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetRightInPositionToHandleClass]  
    @ClassID int,
    @UserName nvarchar(50),
    @Act_ID int,
    @AllowAccess bit output
/*

======================================================================
功能:    得到职位中@UserName是否能对@ClassID组进行@Act_ID操作
参数:
    @ClassID int             :    组（类）ID
    @UserName nvarchar(255)    :    用户名
    @Act_ID int            :    权利代号

备注:    继承指是否继承在父组中的权利(在父组中有权利即在此组中有权利)
======================================================================

*/
AS
BEGIN
SET NOCOUNT ON

SET @AllowAccess = 0;

--得到所有被访问组及其父组
DECLARE @pteam TABLE (pid int);
with Class_ParentTree ( ClassID )
as
(
    select @ClassID

    union all    

    select c.ClassParentID
    from Class_ParentTree p inner join dbo.UDS_Class c on p.ClassID = c.ClassID and c.ClassID <> c.ClassParentID
)
insert into @pteam ( pid )
select ClassID
from Class_ParentTree;

--得到所有父组中拥有此权利的职位ID
DECLARE @pAssingPosition TABLE (Positionid int);
INSERT INTO @pAssingPosition 
    SELECT Position_ID 
        FROM 
            dbo.UDS_Assign_Rule a,
            dbo.uds_class b,
            @pteam p
        WHERE
            b.classid = a.team_id 
            and b.classid = p.pid
            and based_on = 1 /* position */
            and a.act_id = @Act_id

--返回用户是否在有权利的职位ID中
IF EXISTS (
    SELECT 1
    FROM
        dbo.uds_staff a,
        dbo.uds_staff_in_position c,
        @pAssingPosition b 
    WHERE
        a.staff_name = @username
        and a.staff_id = c.staff_id
        and c.Position_ID = b.Positionid
)
BEGIN
    SET @AllowAccess = 1;
END

END
GO